import { getDB } from '../../db';

/**
 * 视频录制与编辑  列表+条件查询
 */
export const getVideoRecording = (queryParams: LX.DBService.VideoInfoQuery) => {
  const db = getDB();
  let query = `
    SELECT "id", "video_name", "video_duration", "file_size", "start_time", "end_time", "channel", "status", "download_status", "save_path", "create_time", "create_user"
    FROM "main"."video_info" WHERE "status" = 0
  `;

  const conditions: string[] = [];
  const params: any = {};

  if (queryParams.startTime) {
    conditions.push(`"create_time" >= @startTime`);
    params['startTime'] = queryParams.startTime;
  }
  if (queryParams.endTime) {
    conditions.push(`"create_time" <= @endTime`);
    params['endTime'] = queryParams.endTime;
  }
  if (queryParams.channel) {
    conditions.push(`"channel" = @channel`);
    params['channel'] = queryParams.channel;
  }
  if (queryParams.videoName) {
    conditions.push(`"video_name" like '%${queryParams.videoName}%' `);
  }

  if (conditions.length > 0) {
    query += ' AND ' + conditions.join(' AND ');
  }

  query += ' ORDER BY "create_time" DESC';

  if (queryParams.current !== undefined && queryParams.page !== undefined) {
    query += ' LIMIT @current, @page';
    params['current'] = queryParams.current;
    params['page'] = queryParams.page;
  }
  return db.prepare(query).all(params);
};

/**
 * 视频录制与编辑  列表总条数+条件查询
 */
export const getVideoRecordingCount = (queryParams: LX.DBService.VideoInfoCountQuery) => {
  const db = getDB();
  let query = `
    SELECT count(id) as count FROM "main"."video_info" WHERE "status" = 0
  `;

  const conditions: string[] = [];
  const params: any = {};

  if (queryParams.startTime) {
    conditions.push(`"create_time" >= @startTime`);
    params['startTime'] = queryParams.startTime;
  }
  if (queryParams.endTime) {
    conditions.push(`"create_time" <= @endTime`);
    params['endTime'] = queryParams.endTime;
  }
  if (queryParams.channel) {
    conditions.push(`"channel" = @channel`);
    params['channel'] = queryParams.channel;
  }

  if (queryParams.videoName) {
    conditions.push(`"video_name" like '%${queryParams.videoName}%' `);
  }

  if (conditions.length > 0) {
    query += ' AND ' + conditions.join(' AND ');
  }

  return db.prepare(query).get(params);
};

/**
 * 视频录制与编辑 根据视频ID查询是否有标注信息 count > 0 即有
 */
export const videoMarkInfoCountStatement = () => {
  const db = getDB();
  let query = `
SELECT count(t.id) as count FROM (
	SELECT m.id,m.label FROM video_mark_info m WHERE m.status = 0 AND m.video_info_id = ?
	UNION all
 SELECT m."id",m."label" FROM video_info v
	JOIN video_real_mark_info m ON m.start_time >= v.start_time
	AND m.start_time <= v.end_time AND m.status = 0 AND v.id = ?
	) t
`;
  return db.prepare<[number]>(query);
};

/**
 * 添加视频信息 支持批量添加
 */
export const createAddVideoInfoStatement = () => {
  const db = getDB();
  return db.prepare<[LX.DBService.VideoInfo]>(`
      INSERT INTO "main"."video_info"("id", "video_name", "video_duration", "file_size", "start_time", "end_time", "channel", "status", "download_status", "save_path","create_time", "create_user")
      values(@id,@videoName,@videoDuration,@fileSize,@startTime,@endTime,@channel,@status,@downloadStatus,@savePath,@createTime,@createUser)`);
};

/*获取最大id*/
export const getMaxId = () => {
  const db = getDB();
  return db.prepare<[], LX.DBService.MarkInfoMaxId>(`SELECT max(cast(id as int)) id FROM "main"."video_info" `);
};

export const getMaxIdOfToDay = () => {
  const db = getDB();
  return db.prepare<[], LX.DBService.MarkInfoMaxId>(`SELECT max(cast(id as int)) id FROM "main"."video_info" `);
};

//获取channel 值
export const getChannelList = () => {
  const db = getDB();
  return db.prepare<[], LX.DBService.channel>(
    `SELECT distinct channel as label, channel as value FROM "main"."video_info" where status = 0  order by channel `,
  );
};

/**
 * 视频录制与编辑  删除【逻辑删除】
 */
export const VideoRecordingDelete = () => {
  const db = getDB();
  return db.prepare<[LX.DBService.VideoInfoUpdate]>(
    'UPDATE "main"."video_info"  set "status" = @status where "id" = @id',
  );
};
